package cn.com.acca.ma.dao.impl;

import cn.com.acca.ma.common.util.DateUtil;
import cn.com.acca.ma.model.StockWeek;

import java.math.BigDecimal;
import java.util.*;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import cn.com.acca.ma.dao.StockWeekDao;
import cn.com.acca.ma.jpa.util.JpaUtil;
import cn.com.acca.ma.pojo.*;

public class StockWeekDaoImpl extends BaseDaoImpl<StockWeekDaoImpl> implements StockWeekDao {

    public StockWeekDaoImpl() {
        super();
    }

    /*********************************************************************************************************************
     *
     * 									                            更新某一周的数据
     *
     *********************************************************************************************************************/
    public void writeStockWeekByDate(String beginDate, String endDate) {
        logger.info("开始计算所有股票在日期【" + beginDate + "】和【" + endDate + "】之间，"
                + "周线级别的基础数据");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query query = em.createNativeQuery("{call pkg_week.WRITE_STOCK_WEEK_BY_DATE(?,?)}");
        query.setParameter(1, beginDate);
        query.setParameter(2, endDate);
        query.executeUpdate();
        em.getTransaction().commit();
        em.close();
    }

    public void writeStockWeekMAByDate(String beginDate, String endDate) {
        logger.info("开始计算所有股票在日期【" + beginDate + "】和【" + endDate + "】之间，"
                + "周线级别的移动平均线");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query query = em.createNativeQuery("{call pkg_week.WRITE_MA_BY_DATE(?,?)}");
        query.setParameter(1, beginDate);
        query.setParameter(2, endDate);
        query.executeUpdate();
        em.getTransaction().commit();
        em.close();
    }

    public void writeStockWeekKDByDate(String beginDate, String endDate) {
        logger.info("开始计算所有股票在日期【" + beginDate + "】和【" + endDate + "】之间，"
                + "周线级别的KD指标");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryRsv = em.createNativeQuery("{call pkg_week.WRITE_WEEK_KD_BY_DATE_RSV(?,?)}");
        queryRsv.setParameter(1, beginDate);
        queryRsv.setParameter(2, endDate);
        queryRsv.executeUpdate();
        em.getTransaction().commit();
        logger.info("所有股票在日期【" + beginDate + "】和【" + endDate + "】之间，周线级别的KD指标中的RSV计算完成");

        em.getTransaction().begin();
        Query queryK = em.createNativeQuery("{call pkg_week.WRITE_WEEK_KD_BY_DATE_K(?,?)}");
        queryK.setParameter(1, beginDate);
        queryK.setParameter(2, endDate);
        queryK.executeUpdate();
        em.getTransaction().commit();
        logger.info("所有股票在日期【" + beginDate + "】和【" + endDate + "】之间，周线级别的KD指标中的K计算完成");

        em.getTransaction().begin();
        Query queryD = em.createNativeQuery("{call pkg_week.WRITE_WEEK_KD_BY_DATE_D(?,?)}");
        queryD.setParameter(1, beginDate);
        queryD.setParameter(2, endDate);
        queryD.executeUpdate();
        em.getTransaction().commit();
        em.close();
        logger.info("所有股票在日期【" + beginDate + "】和【" + endDate + "】之间，周线级别的KD指标中的D计算完成");
    }

    public void writeStockWeekUpDownByDate(String allStockWeekUpdownBeginDate, String allStockWeekUpdownEndDate) {
        logger.info("开始计算周线级别，所有股票在开始时间：【" + allStockWeekUpdownBeginDate + "】"
                + "和结束时间：【" + allStockWeekUpdownEndDate + "】的up_down字段的值");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryUpDown = em.createNativeQuery("{call pkg_week.WRITE_WEEK_UP_DOWN_BY_DATE(?,?)}");
        queryUpDown.setParameter(1, allStockWeekUpdownBeginDate);
        queryUpDown.setParameter(2, allStockWeekUpdownEndDate);
        queryUpDown.executeUpdate();
        em.getTransaction().commit();
        em.close();

        logger.info("周线级别，所有股票在开始时间：【" + allStockWeekUpdownBeginDate + "】"
                + "和结束时间：【" + allStockWeekUpdownEndDate + "】的up_down字段的值计算完成");
    }

    /**
     * 更新所有股票某一周的MACD数据
     *
     * @param stockWeekBeginDate
     * @param stockWeekEndDate
     */
    public void writeStockWeekMACDByDate(String stockWeekBeginDate, String stockWeekEndDate) {
        logger.info("开始更新周线级别，日期【" + stockWeekBeginDate + "】至【" + stockWeekEndDate + "】，MACD的ema指标");
        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryEma = em.createNativeQuery("{call pkg_week.WRITE_WEEK_MACD_EMA_BY_DATE(?,?)}");
        queryEma.setParameter(1, stockWeekBeginDate);
        queryEma.setParameter(2, stockWeekEndDate);
        queryEma.executeUpdate();
        em.getTransaction().commit();
        logger.info("更新周线级别，日期【" + stockWeekBeginDate + "】至【" + stockWeekEndDate + "】，MACD的ema指标完成");

        logger.info("开始更新周线级别，日期【" + stockWeekBeginDate + "】至【" + stockWeekEndDate + "】，MACD的dif指标");
        em.getTransaction().begin();
        Query queryDif = em.createNativeQuery("{call pkg_week.WRITE_WEEK_MACD_DIF_BY_DATE(?,?)}");
        queryDif.setParameter(1, stockWeekBeginDate);
        queryDif.setParameter(2, stockWeekEndDate);
        queryDif.executeUpdate();
        em.getTransaction().commit();
        logger.info("更新周线级别，日期【" + stockWeekBeginDate + "】至【" + stockWeekEndDate + "】，MACD的dif指标完成");

        logger.info("开始更新周线级别，日期【" + stockWeekBeginDate + "】至【" + stockWeekEndDate + "】，MACD的dea指标");
        em.getTransaction().begin();
        Query queryDea = em.createNativeQuery("{call pkg_week.WRITE_WEEK_MACD_DEA_BY_DATE(?,?)}");
        queryDea.setParameter(1, stockWeekBeginDate);
        queryDea.setParameter(2, stockWeekEndDate);
        queryDea.executeUpdate();
        em.getTransaction().commit();
        logger.info("更新周线级别，日期【" + stockWeekBeginDate + "】至【" + stockWeekEndDate + "】，MACD的dea指标完成");

        logger.info("开始更新周线级别，日期【" + stockWeekBeginDate + "】至【" + stockWeekEndDate + "】，MACD的macd指标");
        em.getTransaction().begin();
        Query queryMacd = em.createNativeQuery("{call pkg_week.WRITE_WEEK_MACD_BY_DATE(?,?)}");
        queryMacd.setParameter(1, stockWeekBeginDate);
        queryMacd.setParameter(2, stockWeekEndDate);
        queryMacd.executeUpdate();
        em.getTransaction().commit();
        em.close();
        logger.info("更新周线级别，日期【" + stockWeekBeginDate + "】至【" + stockWeekEndDate + "】，MACD的指标完成");
    }

    /**
     * 更新所有股票某一周的CHANGE_RANGE_EX_RIGHT字段
     *
     * @param allStockWeekChangeRangeExRightBeginDate
     * @param allStockWeekChangeRangeExRightEndDate
     */
    public void writeStockWeekChangeRangeExRightByDate(String allStockWeekChangeRangeExRightBeginDate, String allStockWeekChangeRangeExRightEndDate) {
        logger.info("开始计算周线级别，所有股票在开始时间：【" + allStockWeekChangeRangeExRightBeginDate + "】"
                + "和结束时间：【" + allStockWeekChangeRangeExRightEndDate + "】的CHANGE_RANGE_EX_RIGHT字段的值");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryUpDown = em.createNativeQuery("{call pkg_week.WRITE_WEEK_C_R_E_R_BY_DATE(?,?)}");
        queryUpDown.setParameter(1, allStockWeekChangeRangeExRightBeginDate);
        queryUpDown.setParameter(2, allStockWeekChangeRangeExRightEndDate);
        queryUpDown.executeUpdate();
        em.getTransaction().commit();
        em.close();

        logger.info("周线级别，所有股票在开始时间：【" + allStockWeekChangeRangeExRightBeginDate + "】"
                + "和结束时间：【" + allStockWeekChangeRangeExRightEndDate + "】的CHANGE_RANGE_EX_RIGHT字段的值计算完成");
    }

    /**
     * 更新所有股票某一周的与布林带相关的字段
     *
     * @param allStockWeekBollBeginDate
     * @param allStockWeekBollEndDate
     */
    public void writeStockWeekBollByDate(String allStockWeekBollBeginDate, String allStockWeekBollEndDate) {
        logger.info("开始计算周线级别，所有股票在开始时间：【" + allStockWeekBollBeginDate + "】"
                + "和结束时间：【" + allStockWeekBollEndDate + "】的与布林带相关的字段的值");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryUpDown = em.createNativeQuery("{call pkg_week.CAL_BOLL_BY_DATE(?,?)}");
        queryUpDown.setParameter(1, allStockWeekBollBeginDate);
        queryUpDown.setParameter(2, allStockWeekBollEndDate);
        queryUpDown.executeUpdate();
        em.getTransaction().commit();
        em.close();

        logger.info("周线级别，所有股票在开始时间：【" + allStockWeekBollBeginDate + "】"
                + "和结束时间：【" + allStockWeekBollEndDate + "】的与布林带相关的字段的值计算完成");
    }

    /**
     * 更新所有股票某一周的与hei_kin_ashi的字段
     *
     * @param allStockWeekBollBeginDate
     * @param allStockWeekBollEndDate
     */
    public void writeStockWeekHeiKinAshiByDate(String allStockWeekBollBeginDate, String allStockWeekBollEndDate) {
        logger.info("开始计算周线级别，所有股票在开始时间：【" + allStockWeekBollBeginDate + "】"
                + "和结束时间：【" + allStockWeekBollEndDate + "】的与hei_kin_ashi相关的字段的值");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryUpDown = em.createNativeQuery("{call pkg_week.CAL_STOCK_WEEK_HA_BY_DATE(?,?)}");
        queryUpDown.setParameter(1, allStockWeekBollBeginDate);
        queryUpDown.setParameter(2, allStockWeekBollEndDate);
        queryUpDown.executeUpdate();
        em.getTransaction().commit();
        em.close();

        logger.info("周线级别，所有股票在开始时间：【" + allStockWeekBollBeginDate + "】"
                + "和结束时间：【" + allStockWeekBollEndDate + "】的与hei_kin_ashi相关的字段的值计算完成");
    }

    /*********************************************************************************************************************
     *
     * 									                            根据一周数据更新KD图表
     *
     *********************************************************************************************************************/
    @SuppressWarnings("unchecked")
    public List<String> getDateByConditionForKD(boolean multithreading, String beginDate, String endDate) {
        logger.info("get date by condition begin");

        List<String> list = new ArrayList<String>();
        String queryString = new String("select distinct to_char(date,'yyyy-mm-dd') as date from StockIndex t where code='000001' and date " +
                "between to_date(" + beginDate + ",'yyyy-mm-dd') and to_date(" + endDate + ",'yyyy-mm-dd') order by date asc");

        EntityManager entityManager = null;
        Query queryDateByCondition;
        if (multithreading) {
            entityManager = JpaUtil.newEntityManager();
            entityManager.getTransaction().begin();
            queryDateByCondition = entityManager.createQuery(queryString);
        } else {
            em = JpaUtil.currentEntityManager();
            em.getTransaction().begin();
            queryDateByCondition = em.createQuery(queryString);
        }

        list = queryDateByCondition.getResultList();

        if (multithreading) {
            entityManager.getTransaction().commit();
            entityManager.close();
        } else {
            em.getTransaction().commit();
            em.close();
        }

        logger.info("get date by condition finish");
        return list;
    }

    public double obtainWeekK(String weekBegin, String weekEnd) {
        logger.info("obtain week k d begin");

        double k;
        String queryString = new String("select sum(t.k)/count(t.code_) from Stock_Week t where " +
                "t.Begin_Date between to_date('" + weekEnd + "','yyyy-mm-dd') and " +
                "to_date('" + weekBegin + "','yyyy-mm-dd')");
        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryK = em.createNativeQuery(queryString);
        if (null == queryK.getResultList().get(0)) {
            return -1;
        }
        k = Double.parseDouble(queryK.getResultList().get(0).toString());
        em.getTransaction().commit();
        em.close();

        logger.info("obtain week k d finish");
        return k;
    }

    public double obtainWeekD(String weekBegin, String weekEnd) {
        logger.info("obtain week k d begin");

        double d;
        String queryString = new String("select sum(t.d)/count(t.code_) from Stock_Week t where " +
                "t.Begin_Date between to_date('" + weekEnd + "','yyyy-mm-dd') and " +
                "to_date('" + weekBegin + "','yyyy-mm-dd')");
        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryD = em.createNativeQuery(queryString);
        if (null == queryD.getResultList().get(0)) {
            return -1;
        }
        d = Double.parseDouble(queryD.getResultList().get(0).toString());
        em.getTransaction().commit();
        em.close();

        logger.info("obtain week k d finish");
        return d;
    }

    /**
     * 为创建周线级别KD图表，获取周线级别每一周的所有股票的平均收盘价
     *
     * @param multithreading
     * @param weekBegin
     * @param weekEnd
     * @return
     */
    @SuppressWarnings({"rawtypes"})
    public List getAverageCloseWeek(boolean multithreading, String weekBegin, String weekEnd) {
        logger.info("getAverageCloseWeek begin");

//		final List<HashMap> list=new ArrayList<HashMap>();
//		
//		em = JpaUtil.currentEntityManager();
//		em.getTransaction().begin();
//		try{
//			SessionImplementor session =em.unwrap(SessionImplementor.class);
//	        Connection metaDataConnection=session.connection();
//	        CallableStatement ocs=metaDataConnection.prepareCall("{call PKG_WEEKEND.CALCULATE_AVG_WEEKEND_CLOSE(?,?,?)}");
//	        ocs.setString(1, weekendBegin);
//	        ocs.setString(2, weekendEnd);
//	        ocs.registerOutParameter(3, OracleTypes.ARRAY,"T_STOCKWEEKEND_AVGCLOSE_ARRAY");
//	        ocs.execute();
//	        ARRAY ary = (ARRAY) ocs.getArray(3);
//	        Datum[] dtm = ary.getOracleArray();
//			for (int i = 0; i < dtm.length; i++) {
//				STRUCT st = (STRUCT) dtm[i];
//				Object obj[] = st.getAttributes();
//				logger.info(obj[0].getClass());
//				System.out.println(obj[0] + "---" + obj[1]);
//			}
//		}catch(SQLException e){
//			e.printStackTrace();
//		}


        List list = new ArrayList<String>();
        // 这段代码是从STOCK_WEEKEND表中查询数据，返回星期数和平均收盘价。现在决定改用日线级别的收盘价
//		String queryString = new String("select to_char(t.weekend_begin_date,'iw'),avg(t.weekend_close) from STOCK_WEEKEND t where " +
//				"t.weekend_begin_date between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') " +
//				"group by to_char(t.weekend_begin_date,'iw') order by to_char(t.weekend_begin_date,'iw')");
        String queryString = new String("select t.date_,avg(t.close_price) from stock_transaction_data t " +
                "where t.date_ between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') " +
                "group by t.date_ order by t.date_ asc");

        EntityManager entityManager = null;
        Query query;
        if (multithreading) {
            entityManager = JpaUtil.newEntityManager();
            entityManager.getTransaction().begin();
            query = entityManager.createNativeQuery(queryString);
        } else {
            em = JpaUtil.currentEntityManager();
            em.getTransaction().begin();
            query = em.createNativeQuery(queryString);
        }

        query.setParameter(1, weekBegin);
        query.setParameter(2, weekEnd);
        list = query.getResultList();

        if (multithreading) {
            entityManager.getTransaction().commit();
            entityManager.close();
        } else {
            em.getTransaction().commit();
            em.close();
        }


//		Object[] firstObj=(Object[])list.get(0);
//		Date firstDate=(Date) firstObj[0];
//		
//		Date weekendBeginDate=(Date) firstObj[0];
//		BigDecimal sumClose=(BigDecimal)firstObj[1];
//		Integer number=(Integer)firstObj[2];
//		
//		for(int i=1;i<list.size();i++){
//			Object[] secondObj=(Object[])list.get(i);
//			Date secondDate=(Date) secondObj[0];
//			if(DateUtil.isSameWeekend(DateUtil.dateToString(firstDate), DateUtil.dateToString(secondDate))){
//				weekendBeginDate=(Date)secondObj[0];
//				sumClose.add((BigDecimal)secondObj[1]);
//				number+=number+(Integer)secondObj[2];
//			}
//			
//		}


        logger.info("getAverageCloseWeek finish");
        return list;
    }

    /**
     * 之前本打算使用的算法，现在已经弃用
     *
     * @param weekBegin
     * @param weekEnd
     * @return
     */
    @SuppressWarnings("rawtypes")
    public List getOldAverageCloseWeek(String weekBegin, String weekEnd) {
        logger.info("getAverageCloseWeek begin");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer(
                "select * from (select distinct t.weekend_begin_date from "
                        + "STOCK_WEEK t union select distinct t.weekend_end_date from STOCK_WEEK t) o "
                        + "where o.weekend_begin_date between to_date(?,'yyyy-mm-dd') and "
                        + "to_date(?,'yyyy-mm-dd') order by o.weekend_begin_date asc");
        Query query = em.createNativeQuery(queryString.toString());
        query.setParameter(1, weekBegin);
        query.setParameter(2, weekEnd);
        List list = query.getResultList();
        em.getTransaction().commit();
        em.close();

        logger.info("getAverageCloseWeek finish");
        return list;
    }

    /**
     * 根据开始日期和结束日期，按周分组，返回平均收盘价、平均上轨值、平均中轨值和平均下轨值
     *
     * @param weekBeginDate
     * @param weekEndDate
     * @return
     */
    public List<StockWeekAverageBollPojo> findAvgClosePriceAndAvgUPAndAvgMBAndAvgDNGroupByYearAndWeek(String weekBeginDate, String weekEndDate) {
        logger.info("开始根据开始日期和结束日期，按周分组，返回平均收盘价、平均上轨值、平均中轨值和平均下轨值");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer(
                "select siw.begin_date, siw.end_date, avg(sw.close_price) averageClosePrice, " +
                        "avg(sw.up) averageUP, avg(sw.mb) averageMB, avg(sw.dn_) averageDN " +
                        "from stock_week sw " +
                        "join stock_index_week siw on siw.begin_date>=sw.begin_date and siw.end_date<=sw.end_date " +
                        "where siw.begin_date>=to_date(?,'yyyy-mm-dd') and siw.end_date<=to_date(?,'yyyy-mm-dd') " +
                        "group by siw.begin_date, siw.end_date " +
                        "order by siw.begin_date desc");
        Query query = em.createNativeQuery(queryString.toString());
        query.setParameter(1, weekBeginDate);
        query.setParameter(2, weekEndDate);
        List list = query.getResultList();
        List<StockWeekAverageBollPojo> stockWeekAverageBollPojoList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Object object = list.get(i);
            Object[] objectArray = (Object[]) object;
            StockWeekAverageBollPojo stockWeekAverageBollPojo = new StockWeekAverageBollPojo();
            stockWeekAverageBollPojo.setBeginDate((Date) objectArray[0]);
            stockWeekAverageBollPojo.setEndDate((Date) objectArray[1]);
            stockWeekAverageBollPojo.setAverageClosePrice((BigDecimal) objectArray[2]);
            stockWeekAverageBollPojo.setAverageUP((BigDecimal) objectArray[3]);
            stockWeekAverageBollPojo.setAverageMB((BigDecimal) objectArray[4]);
            stockWeekAverageBollPojo.setAverageDN((BigDecimal) objectArray[5]);
            stockWeekAverageBollPojoList.add(stockWeekAverageBollPojo);
        }
        em.getTransaction().commit();
        em.close();

        logger.info("根据开始日期和结束日期，按周分组，返回平均收盘价、平均上轨值、平均中轨值和平均下轨值完成");
        return stockWeekAverageBollPojoList;
    }

    /**
     * 根据开始日期和结束日期，按周分组，返回收盘价突破布林带上轨和下轨的股票的百分比
     *
     * @param weekBeginDate
     * @param weekEndDate
     * @return
     */
    public List<StockWeekClosePriceUpDnBollPercentagePojo> findClosePriceUpDonBollPercentage(String weekBeginDate, String weekEndDate) {
        logger.info("开始根据开始日期和结束日期，按周分组，返回收盘价突破布林带上轨和下轨的股票的百分比");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer(
                "select siw.begin_date, siw.end_date, avg(sw.close_price), " +
                        "(select count(*) from stock_week sw1 where sw1.begin_date>=siw.begin_date and sw1.end_date<=siw.end_date and sw1.close_price>sw1.up)/" +
                        "(select count(*) from stock_week sw1 where sw1.begin_date>=siw.begin_date and sw1.end_date<=siw.end_date)*100, " +
                        "(select count(*) from stock_week sw1 where sw1.begin_date>=siw.begin_date and sw1.end_date<=siw.end_date and sw1.close_price<sw1.dn_)/" +
                        "(select count(*) from stock_week sw1 where sw1.begin_date>=siw.begin_date and sw1.end_date<=siw.end_date)*100 " +
                        "from stock_week sw " +
                        "join stock_index_week siw on siw.begin_date>=sw.begin_date and siw.end_date<=sw.end_date " +
                        "where siw.begin_date>=to_date(?,'yyyy-mm-dd') and siw.end_date<=to_date(?,'yyyy-mm-dd') " +
                        "group by siw.begin_date, siw.end_date " +
                        "order by siw.begin_date desc");
        Query query = em.createNativeQuery(queryString.toString());
        query.setParameter(1, weekBeginDate);
        query.setParameter(2, weekEndDate);
        List list = query.getResultList();
        List<StockWeekClosePriceUpDnBollPercentagePojo> stockWeekClosePriceUpDnBollPercentagePojoArrayList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Object object = list.get(i);
            Object[] objectArray = (Object[]) object;
            StockWeekClosePriceUpDnBollPercentagePojo stockWeekClosePriceUpDnBollPercentagePojo = new StockWeekClosePriceUpDnBollPercentagePojo();
            stockWeekClosePriceUpDnBollPercentagePojo.setBeginDate((Date) objectArray[0]);
            stockWeekClosePriceUpDnBollPercentagePojo.setEndDate((Date) objectArray[1]);
            stockWeekClosePriceUpDnBollPercentagePojo.setAverageClosePirce((BigDecimal) objectArray[2]);
            stockWeekClosePriceUpDnBollPercentagePojo.setUpBollPercentage((BigDecimal) objectArray[3]);
            stockWeekClosePriceUpDnBollPercentagePojo.setDnBollPercentage((BigDecimal) objectArray[4]);
            stockWeekClosePriceUpDnBollPercentagePojoArrayList.add(stockWeekClosePriceUpDnBollPercentagePojo);
        }
        em.getTransaction().commit();
        em.close();

        logger.info("根据开始日期和结束日期，按周分组，返回收盘价突破布林带上轨和下轨的股票的百分比完成");
        return stockWeekClosePriceUpDnBollPercentagePojoArrayList;
    }

    /**
     * 查找beginDate和endDate之间的，所有MA和收盘价
     *
     * @param beginDate
     * @param endDate
     * @return
     */
    @Override
    public List<StockWeekAverageClosePriceAndAllAverageMa> findAllAverageMaAndAverageClosePriceByBeginDateAndEndDate(String beginDate, String endDate) {
        logger.info("查找beginDate【" + beginDate + "】和endDate【" + endDate + "】之间的，所有MA和收盘价");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer(
                "select avg(t.ma5), avg(t.ma10), avg(t.ma20), avg(t.ma60), avg(t.ma120), avg(t.ma250), avg(t.close_price), to_date(?,'yyyy-mm-dd') endDate " +
                        "from stock_week t " +
                        "where t.begin_date>=to_date(?,'yyyy-mm-dd') and t.end_date<=to_date(?,'yyyy-mm-dd')");
        Query query = em.createNativeQuery(queryString.toString());
        query.setParameter(1, endDate);
        query.setParameter(2, beginDate);
        query.setParameter(3, endDate);
        List list = query.getResultList();
        List<StockWeekAverageClosePriceAndAllAverageMa> stockWeekAverageClosePriceAndAllAverageMaList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Object object = list.get(i);
            Object[] objectArray = (Object[]) object;
            StockWeekAverageClosePriceAndAllAverageMa stockWeekAverageClosePriceAndAllAverageMa = new StockWeekAverageClosePriceAndAllAverageMa();
            stockWeekAverageClosePriceAndAllAverageMa.setAverageMA5(new BigDecimal(objectArray[0].toString()));
            stockWeekAverageClosePriceAndAllAverageMa.setAverageMA10(new BigDecimal(objectArray[1].toString()));
            stockWeekAverageClosePriceAndAllAverageMa.setAverageMA20(new BigDecimal(objectArray[2].toString()));
            stockWeekAverageClosePriceAndAllAverageMa.setAverageMA60(new BigDecimal(objectArray[3].toString()));
            stockWeekAverageClosePriceAndAllAverageMa.setAverageMA120(new BigDecimal(objectArray[4].toString()));
            stockWeekAverageClosePriceAndAllAverageMa.setAverageMA250(new BigDecimal(objectArray[5].toString()));
            stockWeekAverageClosePriceAndAllAverageMa.setAverageClosePrice(new BigDecimal(objectArray[6].toString()));
            stockWeekAverageClosePriceAndAllAverageMa.setEndDate((Date) objectArray[7]);
            stockWeekAverageClosePriceAndAllAverageMaList.add(stockWeekAverageClosePriceAndAllAverageMa);
        }
        em.getTransaction().commit();
        em.close();

        logger.info("查找beginDate【" + beginDate + "】和endDate【" + endDate + "】之间的，所有MA和收盘价完成");
        return stockWeekAverageClosePriceAndAllAverageMaList;
    }

    /**
     * 根据开始时间和结束时间，查找ha_week_close_price的平均值，并按照end_date升序排列
     *
     * @param beginDate
     * @param endDate
     * @return
     */
    @Override
    public List<StockWeek> findAverageHaClosePriceAndAverageHaWeekClosePriceByBeginDateAndEndDateOrderByEndDateAsc(String beginDate, String endDate) {
        logger.info("根据开始时间【" + beginDate + "】和结束时间【" + endDate + "】，查找ha_week_close_price的平均值，并按照end_date升序排列");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer(
                "select distinct t.begin_date, t.end_date from stock_index_week t " +
                        "where t.begin_date>=to_date(?,'yyyy-mm-dd') and t.end_date<=to_date(?,'yyyy-mm-dd')");
        Query query = em.createNativeQuery(queryString.toString());
        query.setParameter(1, beginDate);
        query.setParameter(2, endDate);
        List list = query.getResultList();
        List<StockWeek> stockWeekList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Object object = list.get(i);
            Object[] objectArray = (Object[]) object;
            Date theBeginDate = (DateUtil.stringToDate(objectArray[0].toString()));
            Date theEndDate = (DateUtil.stringToDate(objectArray[1].toString()));

            queryString = new StringBuffer("select avg(t.ha_week_close_price), avg(t.close_price) from STOCK_WEEK t " +
                    "where t.begin_date>=? and t.end_date<=?");
            query = em.createNativeQuery(queryString.toString());
            query.setParameter(1, theBeginDate);
            query.setParameter(2, theEndDate);
            object = query.getSingleResult();
            objectArray = (Object[]) object;
//            System.out.println(theEndDate);
            BigDecimal averageHaWeekClosePrice= new BigDecimal(objectArray[0].toString());
            BigDecimal averageClosePrice = new BigDecimal(objectArray[1].toString());
            StockWeek stockWeek = new StockWeek();
            stockWeek.setHaWeekClosePrice(averageHaWeekClosePrice);
            stockWeek.setClosePrice(averageClosePrice);
            stockWeek.setEndDate(theEndDate);
            stockWeekList.add(stockWeek);
        }
        em.getTransaction().commit();
        em.close();

        logger.info("根据开始时间【" + beginDate + "】和结束时间【" + endDate + "】，查找ha_week_close_price的平均值，并按照end_date升序排列完成");
        return stockWeekList;
    }

    /*********************************************************************************************************************
     *
     * 									                           更新所有的数据
     *
     *********************************************************************************************************************/
    public void writeStockWeek() {
        logger.info("开始计算所有股票，在周线级别，所有周的基础数据");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query query = em.createNativeQuery("{call pkg_week.WRITE_STOCK_WEEK()}");
        query.executeUpdate();
        em.getTransaction().commit();
        em.close();
    }

    public void writeStockWeekMA() {
        logger.info("开始计算周线级别，所有股票的移动平均线");

        em = JpaUtil.currentEntityManager();

        em.getTransaction().begin();
        Query queryMA5 = em.createNativeQuery("{call pkg_week.WRITE_MA5()}");
        queryMA5.executeUpdate();
        em.getTransaction().commit();
        logger.info("周线级别，所有股票的MA5计算完成");

        em.getTransaction().begin();
        Query queryMA10 = em.createNativeQuery("{call pkg_week.WRITE_MA10()}");
        queryMA10.executeUpdate();
        em.getTransaction().commit();
        logger.info("周线级别，所有股票的MA10计算完成");

        em.getTransaction().begin();
        Query queryMA20 = em.createNativeQuery("{call pkg_week.WRITE_MA20()}");
        queryMA20.executeUpdate();
        em.getTransaction().commit();
        logger.info("周线级别，所有股票的MA20计算完成");

        em.getTransaction().begin();
        Query queryMA60 = em.createNativeQuery("{call pkg_week.WRITE_MA60()}");
        queryMA60.executeUpdate();
        em.getTransaction().commit();
        logger.info("周线级别，所有股票的MA60计算完成");

        em.getTransaction().begin();
        Query queryMA120 = em.createNativeQuery("{call pkg_week.WRITE_MA120()}");
        queryMA120.executeUpdate();
        em.getTransaction().commit();
        logger.info("周线级别，所有股票的MA120计算完成");

        em.getTransaction().begin();
        Query queryMA250 = em.createNativeQuery("{call pkg_week.WRITE_MA250()}");
        queryMA250.executeUpdate();
        em.getTransaction().commit();
        em.close();
        logger.info("周线级别，所有股票的MA250计算完成");
    }

    public void writeStockWeekKD() {
        logger.info("开始计算所有股票，在周线级别，所有周的KD指标");

        em = JpaUtil.currentEntityManager();

        em.getTransaction().begin();
        Query queryInit = em.createNativeQuery("{call pkg_week.WRITE_WEEK_KD_INIT()}");
        queryInit.executeUpdate();
        em.getTransaction().commit();
        logger.info("所有股票，在周线级别，所有周的KD指标初始化完成");

        em.getTransaction().begin();
        Query queryRsv = em.createNativeQuery("{call pkg_week.WRITE_WEEK_KD_RSV()}");
        queryRsv.executeUpdate();
        em.getTransaction().commit();
        logger.info("所有股票，在周线级别，所有周的KD指标的rsv完成");

        em.getTransaction().begin();
        Query queryK = em.createNativeQuery("{call pkg_week.WRITE_WEEK_KD_K()}");
        queryK.executeUpdate();
        em.getTransaction().commit();
        logger.info("所有股票，在周线级别，所有周的KD指标的k完成");

        em.getTransaction().begin();
        Query queryD = em.createNativeQuery("{call pkg_week.WRITE_WEEK_KD_D()}");
        queryD.executeUpdate();
        em.getTransaction().commit();
        em.close();
        logger.info("所有股票，在周线级别，所有周的KD指标的d完成");
    }

    public void writeStockWeekUpDown() {
        logger.info("开始计算所有股票在stock_week表中up_down字段的值");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryUpDown = em.createNativeQuery("{call pkg_week.WRITE_WEEK_UP_DOWN()}");
        queryUpDown.executeUpdate();
        em.getTransaction().commit();
        em.close();
    }

    /**
     * 更新STOCK_WEEK表中与MACD相关的所有字段：EMA12，EMA26，D,K
     */
    public void writeStockWeekMACD() {
        logger.info("开始计算周线级别，所有股票的MACD");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryInit = em.createNativeQuery("{call pkg_week.WRITE_WEEK_MACD_INIT()}");
        queryInit.executeUpdate();
        em.getTransaction().commit();
        logger.info("计算周线级别，所有股票的MACD初始化完成");

        logger.info("开始计算周线级别，所有股票MACD的ema");
        em.getTransaction().begin();
        Query queryEma = em.createNativeQuery("{call pkg_week.WRITE_WEEK_MACD_EMA()}");
        queryEma.executeUpdate();
        em.getTransaction().commit();
        logger.info("周线级别，所有股票MACD的ema计算完成");

        logger.info("开始计算周线级别，所有股票MACD的dif");
        em.getTransaction().begin();
        Query queryDif = em.createNativeQuery("{call pkg_week.WRITE_WEEK_MACD_DIF()}");
        queryDif.executeUpdate();
        em.getTransaction().commit();
        logger.info("周线级别，所有股票MACD的dif计算完成");

        logger.info("开始计算周线级别，所有股票MACD的dea");
        em.getTransaction().begin();
        Query queryDea = em.createNativeQuery("{call pkg_week.WRITE_WEEK_MACD_DEA()}");
        queryDea.executeUpdate();
        em.getTransaction().commit();
        logger.info("周线级别，所有股票MACD的dea计算完成");

        logger.info("开始计算周线级别，所有股票MACD的macd");
        em.getTransaction().begin();
        Query queryMacd = em.createNativeQuery("{call pkg_week.WRITE_WEEK_MACD()}");
        queryMacd.executeUpdate();
        em.getTransaction().commit();
        em.close();
        logger.info("周线级别，所有股票MACD的macd计算完成");
    }

    /**
     * 更新STOCK_WEEK表中的CHANGE_RANGE_EX_RIGHT字段
     */
    public void writeStockWeekChangeRangeExRight() {
        logger.info("开始更新STOCK_WEEK表中的CHANGE_RANGE_EX_RIGHT字段");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryUpDown = em.createNativeQuery("{call pkg_week.WRITE_WEEK_C_R_E_R()}");
        queryUpDown.executeUpdate();
        em.getTransaction().commit();
        em.close();
    }

    /**
     * 更新STOCK_WEEK表中的和布林带相关的字段：mb、up、dn
     */
    public void writeStockWeekBoll() {
        logger.info("开始更新STOCK_WEEK表中的和布林带相关的字段：mb、up、dn");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryUpDown = em.createNativeQuery("{call pkg_week.CAL_BOLL()}");
        queryUpDown.executeUpdate();
        em.getTransaction().commit();
        em.close();
    }

    /**
     * 更新STOCK_WEEK表中的hei_kin_ashi相关的字段
     */
    public void writeStockWeekHeiKinAshi() {
        logger.info("开始更新STOCK_WEEK表中的hei_kin_ashi相关的字段");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        Query queryUpDown = em.createNativeQuery("{call pkg_week.CAL_STOCK_WEEK_HA()}");
        queryUpDown.executeUpdate();
        em.getTransaction().commit();
        em.close();
    }

    /************************************** 增量备份STOCK_WEEK表 *****************************************************/
    /**
     * 根据开始时间和结束时间，在STOCK_WEEK表中查找所有END_DATE字段
     *
     * @param beginDate
     * @param endDate
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Date> getDateByCondition(String beginDate, String endDate) {
        logger.info("根据开始时间【" + beginDate + "】和结束时间【" + endDate + "】，"
                + "在STOCK_WEEK表中查找所有END_DATE字段");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer(
                "select distinct t.end_date from STOCK_WEEK t where t.end_date " +
                        "between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') order by t.end_date asc");
        Query query = em.createNativeQuery(queryString.toString());
        query.setParameter(1, beginDate);
        query.setParameter(2, endDate);
        List<Date> list = query.getResultList();
        em.getTransaction().commit();
        em.close();

        return list;
    }

    /**
     * 根据结束时间endDate，在STOCK_WEEK表中查找所有STOCK_WEEK对象
     *
     * @param endDate
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<StockWeek> getStockWeeksByDate(String endDate) {
        logger.info("根据结束时间【" + endDate + "】，在STOCK_WEEK表中查找所有STOCK_WEEK对象");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer("select t from StockWeek t " +
                "where t.endDate=to_date(?,'yyyy-mm-dd')");
        Query query = em.createQuery(queryString.toString());
        query.setParameter(1, endDate);
        List<StockWeek> list = query.getResultList();
        em.getTransaction().commit();
        em.close();

        return list;
    }

    /**
     * 插入StockWeek对象
     *
     * @param stockWeek
     */
    public void saveStockWeek(StockWeek stockWeek) {
        logger.info("插入StockWeek对象");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        em.persist(stockWeek);
        em.getTransaction().commit();
        em.close();
    }

    /**
     * 查询周线级别，收盘价突破布林带上轨和下轨的股票的数量
     *
     * @param beginDate
     * @param endDate
     * @param closePricePercentageFromUpDn
     * @return
     */
    @SuppressWarnings("unchecked")
    public StockWeekBollUpDnCount findStockWeekBollUpDnCount(Date beginDate, Date endDate, Double closePricePercentageFromUpDn) {
        logger.info("查询周线级别，收盘价突破布林带上轨和下轨的股票的数量");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer("select " +
                "(select count(*) " +
                "from stock_week sw " +
                "where sw.begin_date>=? and sw.end_date<=? " +
                "and (sw.close_price>sw.up or (sw.up-sw.close_price)/sw.close_price<=" + closePricePercentageFromUpDn + "/100)) upBollCount, " +
                "(select count(*) " +
                "from stock_week sw " +
                "where sw.begin_date>=? and sw.end_date<=? " +
                "and (sw.close_price<sw.dn_ or (sw.close_price-sw.dn_)/sw.close_price<=" + closePricePercentageFromUpDn + "/100)) dnBollCount " +
                "from dual");
        Query query = em.createNativeQuery(queryString.toString());
        query.setParameter(1, beginDate);
        query.setParameter(2, endDate);
        query.setParameter(3, beginDate);
        query.setParameter(4, endDate);
        Object[] objectArray = (Object[]) query.getSingleResult();
        StockWeekBollUpDnCount stockWeekBollUpDnCount = new StockWeekBollUpDnCount();
        stockWeekBollUpDnCount.setClosePriceUpBollStockCount((BigDecimal) objectArray[0]);
        stockWeekBollUpDnCount.setClosePriceDnBollStockCount((BigDecimal) objectArray[1]);
        em.getTransaction().commit();
        em.close();

        return stockWeekBollUpDnCount;
    }

    /**
     * 查找日期date之前的两周记录的平均K和D，并按照begin_date升序排列
     *
     * @param date
     * @return
     */
    @Override
    public List<StockWeekAverageKD> findLastTwoStockWeekAverageKDByDateOrderByBeginDateAsc(String date) {
        logger.info("查找日期【" + date + "】之前的两周记录的平均K和D，并按照begin_date升序排列");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();

        // 查询上三个星期的开始时间和结束时间，降序排列
        StringBuffer lastTwoStockWeekDateString = new StringBuffer("select * from( " +
                "select distinct siw.begin_date, siw.end_date " +
                "from stock_index_week siw where siw.end_date<to_date(?,'yyyy-mm-dd') order by siw.begin_date desc) " +
                "where rownum<=2");
        Query lastTwoStockWeekDateQuery = em.createNativeQuery(lastTwoStockWeekDateString.toString());
        lastTwoStockWeekDateQuery.setParameter(1, date);
        List<Object> dateList = lastTwoStockWeekDateQuery.getResultList();

        // 查询上三个星期的平均K和D，降序排列
        List<StockWeekAverageKD> stockWeekAverageKDList = new ArrayList<>();
        for (int i = 0; i < dateList.size(); i++) {
            Object[] ObjectArray = (Object[]) dateList.get(i);
            Date beginDate = (Date) ObjectArray[0];
            Date endDate = (Date) ObjectArray[1];
            StringBuffer stockWeekAverageKDString = new StringBuffer("" +
                    "select avg(t.k), avg(t.d) from stock_week t " +
                    "where t.begin_date>=? and t.end_date<=?");
            Query stockWeekAverageKDQuery = em.createNativeQuery(stockWeekAverageKDString.toString());
            stockWeekAverageKDQuery.setParameter(1, beginDate);
            stockWeekAverageKDQuery.setParameter(2, endDate);
            Object[] kdArray = (Object[]) stockWeekAverageKDQuery.getSingleResult();
            StockWeekAverageKD stockWeekAverageKD = new StockWeekAverageKD();
            stockWeekAverageKD.setAverageK((BigDecimal) kdArray[0]);
            stockWeekAverageKD.setAverageD((BigDecimal) kdArray[1]);
            stockWeekAverageKDList.add(stockWeekAverageKD);
        }

        em.getTransaction().commit();
        em.close();

        return stockWeekAverageKDList;
    }

    /**
     * 查找包括日期date在内的之前的两周记录的平均K和D，并按照begin_date升序排列
     *
     * @param date
     * @return
     */
    @Override
    public List<StockWeekAverageKD> findThisTwoStockWeekAverageKDByDateOrderByBeginDateAsc(String date) {
        logger.info("查找包括日期【" + date + "】在内的之前的两周记录的平均K和D，并按照begin_date升序排列");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();

        // 查询上三个星期的开始时间和结束时间，降序排列
        StringBuffer lastTwoStockWeekDateString = new StringBuffer("select * from( " +
                "select distinct siw.begin_date, siw.end_date " +
                "from stock_index_week siw where siw.end_date<=to_date(?,'yyyy-mm-dd') order by siw.begin_date desc) " +
                "where rownum<=2");
        Query lastTwoStockWeekDateQuery = em.createNativeQuery(lastTwoStockWeekDateString.toString());
        lastTwoStockWeekDateQuery.setParameter(1, date);
        List<Object> dateList = lastTwoStockWeekDateQuery.getResultList();

        // 查询上三个星期的平均K和D，降序排列
        List<StockWeekAverageKD> stockWeekAverageKDList = new ArrayList<>();
        for (int i = 0; i < dateList.size(); i++) {
            Object[] ObjectArray = (Object[]) dateList.get(i);
            Date beginDate = (Date) ObjectArray[0];
            Date endDate = (Date) ObjectArray[1];
            StringBuffer stockWeekAverageKDString = new StringBuffer("" +
                    "select avg(t.k), avg(t.d) from stock_week t " +
                    "where t.begin_date>=? and t.end_date<=?");
            Query stockWeekAverageKDQuery = em.createNativeQuery(stockWeekAverageKDString.toString());
            stockWeekAverageKDQuery.setParameter(1, beginDate);
            stockWeekAverageKDQuery.setParameter(2, endDate);
            Object[] kdArray = (Object[]) stockWeekAverageKDQuery.getSingleResult();
            StockWeekAverageKD stockWeekAverageKD = new StockWeekAverageKD();
            stockWeekAverageKD.setAverageK((BigDecimal) kdArray[0]);
            stockWeekAverageKD.setAverageD((BigDecimal) kdArray[1]);
            stockWeekAverageKDList.add(stockWeekAverageKD);
        }

        em.getTransaction().commit();
        em.close();

        return stockWeekAverageKDList;
    }

    /**
     * 查找日期date之前的两周记录的平均hei_kin_ashi收盘价，并按照begin_date升序排列
     *
     * @param date
     * @return
     */
    @Override
    public List<StockWeekAverageHeiKinAshiClosePrice> findLastTwoStockWeekAverageHeiKinAshiClosePriceByDateOrderByBeginDateAsc(String date) {
        logger.info("查找日期【" + date + "】之前的两周记录的平均hei_kin_ashi收盘价，并按照begin_date升序排列");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();

        // 查询上三个星期的开始时间和结束时间，降序排列
        StringBuffer lastTwoStockWeekDateString = new StringBuffer("select * from( " +
                "select distinct siw.begin_date, siw.end_date " +
                "from stock_index_week siw where siw.end_date<to_date(?,'yyyy-mm-dd') order by siw.begin_date desc) " +
                "where rownum<=2");
        Query lastTwoStockWeekDateQuery = em.createNativeQuery(lastTwoStockWeekDateString.toString());
        lastTwoStockWeekDateQuery.setParameter(1, date);
        List<Object> dateList = lastTwoStockWeekDateQuery.getResultList();

        // 查询上三个星期的平均K和D，降序排列
        List<StockWeekAverageHeiKinAshiClosePrice> stockWeekAverageHeiKinAshiClosePriceList = new ArrayList<>();
        for (int i = 0; i < dateList.size(); i++) {
            Object[] ObjectArray = (Object[]) dateList.get(i);
            Date beginDate = (Date) ObjectArray[0];
            Date endDate = (Date) ObjectArray[1];
            StringBuffer stockWeekAverageKDString = new StringBuffer("" +
                    "select avg(t.ha_week_close_price), avg(t.ha_week_open_price) from stock_week t " +
                    "where t.begin_date>=? and t.end_date<=?");
            Query stockWeekAverageHeiKinAshiClosePriceQuery = em.createNativeQuery(stockWeekAverageKDString.toString());
            stockWeekAverageHeiKinAshiClosePriceQuery.setParameter(1, beginDate);
            stockWeekAverageHeiKinAshiClosePriceQuery.setParameter(2, endDate);
            Object[] kdArray = (Object[]) stockWeekAverageHeiKinAshiClosePriceQuery.getSingleResult();
            StockWeekAverageHeiKinAshiClosePrice stockWeekAverageHeiKinAshiClosePrice = new StockWeekAverageHeiKinAshiClosePrice();
            stockWeekAverageHeiKinAshiClosePrice.setAverageHeiKinAshiClosePrice((BigDecimal) kdArray[0]);
            stockWeekAverageHeiKinAshiClosePrice.setAverageHeiKinAshiOpenPrice((BigDecimal) kdArray[1]);
            stockWeekAverageHeiKinAshiClosePriceList.add(stockWeekAverageHeiKinAshiClosePrice);
        }

        em.getTransaction().commit();
        em.close();

        return stockWeekAverageHeiKinAshiClosePriceList;
    }

    /**
     * 查找包括日期date在内的之前的两周记录的平均hei_kin_ashi收盘价，并按照begin_date升序排列
     *
     * @param date
     * @return
     */
    @Override
    public List<StockWeekAverageHeiKinAshiClosePrice> findThisTwoStockWeekAverageHeiKinAshiClosePriceByDateOrderByBeginDateAsc(String date) {
        logger.info("查找包括日期【" + date + "】在内的之前的两周记录的平均hei_kin_ashi收盘价，并按照begin_date升序排列");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();

        // 查询上三个星期的开始时间和结束时间，降序排列
        StringBuffer lastTwoStockWeekDateString = new StringBuffer("select * from( " +
                "select distinct siw.begin_date, siw.end_date " +
                "from stock_index_week siw where siw.end_date<=to_date(?,'yyyy-mm-dd') order by siw.begin_date desc) " +
                "where rownum<=2");
        Query lastTwoStockWeekDateQuery = em.createNativeQuery(lastTwoStockWeekDateString.toString());
        lastTwoStockWeekDateQuery.setParameter(1, date);
        List<Object> dateList = lastTwoStockWeekDateQuery.getResultList();

        // 查询上三个星期的平均K和D，降序排列
        List<StockWeekAverageHeiKinAshiClosePrice> stockWeekAverageHeiKinAshiClosePriceList = new ArrayList<>();
        for (int i = 0; i < dateList.size(); i++) {
            Object[] ObjectArray = (Object[]) dateList.get(i);
            Date beginDate = (Date) ObjectArray[0];
            Date endDate = (Date) ObjectArray[1];
            StringBuffer stockWeekAverageKDString = new StringBuffer("" +
                    "select avg(t.ha_week_close_price), avg(t.ha_week_open_price) from stock_week t " +
                    "where t.begin_date>=? and t.end_date<=?");
            Query stockWeekAverageHeiKinAshiClosePriceQuery = em.createNativeQuery(stockWeekAverageKDString.toString());
            stockWeekAverageHeiKinAshiClosePriceQuery.setParameter(1, beginDate);
            stockWeekAverageHeiKinAshiClosePriceQuery.setParameter(2, endDate);
            Object[] kdArray = (Object[]) stockWeekAverageHeiKinAshiClosePriceQuery.getSingleResult();
            StockWeekAverageHeiKinAshiClosePrice stockWeekAverageHeiKinAshiClosePrice = new StockWeekAverageHeiKinAshiClosePrice();
            stockWeekAverageHeiKinAshiClosePrice.setAverageHeiKinAshiClosePrice((BigDecimal) kdArray[0]);
            stockWeekAverageHeiKinAshiClosePrice.setAverageHeiKinAshiOpenPrice((BigDecimal) kdArray[1]);
            stockWeekAverageHeiKinAshiClosePriceList.add(stockWeekAverageHeiKinAshiClosePrice);
        }

        em.getTransaction().commit();
        em.close();

        return stockWeekAverageHeiKinAshiClosePriceList;
    }

    /**
     * 查找日期date之前的两周记录的平均close_price和MA5，并按照begin_date升序排列
     *
     * @param date
     * @return
     */
    @Override
    public List<StockWeekAverageClosePriceAndMa5> findLastTwoStockWeekAverageClosePriceAndMA5ByDateOrderByBeginDateAsc(String date) {
        logger.info("查找日期【" + date + "】之前的两周记录的平均close_price和MA5，并按照begin_date升序排列");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();

        // 查询上三个星期的开始时间和结束时间，降序排列
        StringBuffer lastTwoStockWeekDateString = new StringBuffer("select * from( " +
                "select distinct siw.begin_date, siw.end_date " +
                "from stock_index_week siw where siw.end_date<to_date(?,'yyyy-mm-dd') order by siw.begin_date desc) " +
                "where rownum<=2");
        Query lastTwoStockWeekDateQuery = em.createNativeQuery(lastTwoStockWeekDateString.toString());
        lastTwoStockWeekDateQuery.setParameter(1, date);
        List<Object> dateList = lastTwoStockWeekDateQuery.getResultList();

        // 查询上三个星期的平均MA5和close_price，降序排列
        List<StockWeekAverageClosePriceAndMa5> stockWeekAverageClosePriceAndMa5ArrayList = new ArrayList<>();
        for (int i = 0; i < dateList.size(); i++) {
            Object[] ObjectArray = (Object[]) dateList.get(i);
            Date beginDate = (Date) ObjectArray[0];
            Date endDate = (Date) ObjectArray[1];
            StringBuffer stockWeekAverageClosePriceAndMa5String = new StringBuffer("" +
                    "select avg(t.close_price), avg(t.ma5) from stock_week t " +
                    "where t.begin_date>=? and t.end_date<=?");
            Query stockWeekAverageClosePriceAndMa5Query = em.createNativeQuery(stockWeekAverageClosePriceAndMa5String.toString());
            stockWeekAverageClosePriceAndMa5Query.setParameter(1, beginDate);
            stockWeekAverageClosePriceAndMa5Query.setParameter(2, endDate);
            Object[] kdArray = (Object[]) stockWeekAverageClosePriceAndMa5Query.getSingleResult();
            StockWeekAverageClosePriceAndMa5 stockWeekAverageClosePriceAndMa5 = new StockWeekAverageClosePriceAndMa5();
            stockWeekAverageClosePriceAndMa5.setAverageClosePrice((BigDecimal) kdArray[0]);
            stockWeekAverageClosePriceAndMa5.setAverageMA5((BigDecimal) kdArray[1]);
            stockWeekAverageClosePriceAndMa5ArrayList.add(stockWeekAverageClosePriceAndMa5);
        }

        em.getTransaction().commit();
        em.close();

        return stockWeekAverageClosePriceAndMa5ArrayList;
    }

    /**
     * 查找包括日期date在内的两周记录的平均close_price和MA5，并按照begin_date升序排列
     *
     * @param date
     * @return
     */
    @Override
    public List<StockWeekAverageClosePriceAndMa5> findThisTwoStockWeekAverageClosePriceAndMA5ByDateOrderByBeginDateAsc(String date) {
        logger.info("查找包括日期【" + date + "】在内的两周记录的平均close_price和MA5，并按照begin_date升序排列");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();

        // 查询上三个星期的开始时间和结束时间，降序排列
        StringBuffer lastTwoStockWeekDateString = new StringBuffer("select * from( " +
                "select distinct siw.begin_date, siw.end_date " +
                "from stock_index_week siw where siw.end_date<=to_date(?,'yyyy-mm-dd') order by siw.begin_date desc) " +
                "where rownum<=2");
        Query lastTwoStockWeekDateQuery = em.createNativeQuery(lastTwoStockWeekDateString.toString());
        lastTwoStockWeekDateQuery.setParameter(1, date);
        List<Object> dateList = lastTwoStockWeekDateQuery.getResultList();

        // 查询上三个星期的平均MA5和close_price，降序排列
        List<StockWeekAverageClosePriceAndMa5> stockWeekAverageClosePriceAndMa5ArrayList = new ArrayList<>();
        for (int i = 0; i < dateList.size(); i++) {
            Object[] ObjectArray = (Object[]) dateList.get(i);
            Date beginDate = (Date) ObjectArray[0];
            Date endDate = (Date) ObjectArray[1];
            StringBuffer stockWeekAverageClosePriceAndMa5String = new StringBuffer("" +
                    "select avg(t.close_price), avg(t.ma5) from stock_week t " +
                    "where t.begin_date>=? and t.end_date<=?");
            Query stockWeekAverageClosePriceAndMa5Query = em.createNativeQuery(stockWeekAverageClosePriceAndMa5String.toString());
            stockWeekAverageClosePriceAndMa5Query.setParameter(1, beginDate);
            stockWeekAverageClosePriceAndMa5Query.setParameter(2, endDate);
            Object[] kdArray = (Object[]) stockWeekAverageClosePriceAndMa5Query.getSingleResult();
            StockWeekAverageClosePriceAndMa5 stockWeekAverageClosePriceAndMa5 = new StockWeekAverageClosePriceAndMa5();
            stockWeekAverageClosePriceAndMa5.setAverageClosePrice((BigDecimal) kdArray[0]);
            stockWeekAverageClosePriceAndMa5.setAverageMA5((BigDecimal) kdArray[1]);
            stockWeekAverageClosePriceAndMa5ArrayList.add(stockWeekAverageClosePriceAndMa5);
        }

        em.getTransaction().commit();
        em.close();

        return stockWeekAverageClosePriceAndMa5ArrayList;
    }

    /**
     * 根据股票代码和时间，查找这只股票在这一周的交易记录
     * @param code
     * @param date
     * @return
     */
    @Override
    public StockWeek findByCodeAndBeginDateAndEndDate(String code, String date) {
        logger.info("根据股票代码【" + code + "】和时间【" + date + "】，查找这只股票在这一周的交易记录");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer("select t from StockWeek t " +
                "where t.code=? and to_date(?, 'yyyy-mm-dd') between t.beginDate and t.endDate");
        Query query = em.createQuery(queryString.toString());
        query.setParameter(1, code);
        query.setParameter(2, date);
        StockWeek stockWeek = (StockWeek) query.getSingleResult();
        em.getTransaction().commit();
        em.close();

        return stockWeek;
    }

    /**
     * 根据开始时间和结束时间，删除记录
     * @param beginDate
     * @param endDate
     */
    @Override
    public void deleteByBeginDateAndEndDate(Date beginDate, Date endDate){
        logger.info("根据开始时间【" + beginDate + "】和结束时间【" + endDate + "】，删除记录");

        em = JpaUtil.currentEntityManager();
        em.getTransaction().begin();
        StringBuffer queryString = new StringBuffer("delete from StockWeek t " +
                "where t.beginDate <= ? and t.endDate >= ?");
        Query query = em.createQuery(queryString.toString());
        query.setParameter(1, beginDate);
        query.setParameter(2, endDate);
        query.executeUpdate();
        em.getTransaction().commit();
        em.close();
    }
}
